package es.ats.codington.festival.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import es.ats.codington.festival.domain.Visitor;
import es.ats.codington.festival.interfaces.daos.IVisitorDAO;
import es.ats.codington.festival.utils.VisitorDBQuery;

/**
 * The Class VisitorDAOImpl.
 *
 * @author 5Cs
 */
public class VisitorDAOImpl implements IVisitorDAO{
	
	/**
	 *  
	 *
	 * @param con the con
	 * @param visitor the visitor
	 * @return the int
	 * @throws Exception the exception
	 * @see es.ats.codington.festival.interfaces.daos.IVisitorDAO#registerVisitorDAO(java.sql.Connection, es.ats.codington.festival.domain.Visitor)
	 */
	public int registerVisitorDAO(Connection con, Visitor visitor) throws Exception{
		int rows = 0;
		int key = 0;
		VisitorDBQuery executeQuery = new VisitorDBQuery();
		
		try{
			//* JSONQuery reader = new JSONQuery(address);
			
		    String query = "INSERT INTO People (Name, LastName, Age, Address, PhoneNumber ) VALUES (?,?,?,?,?);";
			
			//Creating a PreparedStatement with a query 
			PreparedStatement pStmt = con.prepareStatement(query);
			
			//Execute query in DB and get the key
			key = executeQuery.executeRegisterPeople(con, visitor, pStmt);
			
			query = "INSERT INTO Visitor (Username, Password, Admin, VisitorId) VALUES (?,?,?,?);";
			
			//PreparedStatement search the query 
			pStmt = con.prepareStatement(query);
			
			//Execute query in DB
			rows = executeQuery.executeRegisterVisitor(con, visitor, pStmt, key);	
			
		}catch (Exception e){
			System.out.println(e.getMessage());
		}
		return rows;
	}

	/**
	 * Delete visitor dao.
	 *
	 * @param con the con
	 * @param idVisitor the id visitor
	 * @return the int
	 * @throws Exception the exception
	 * @see es.ats.codington.festival.interfaces.daos.IVisitorDAO#deleteVisitorDAO(java.sql.Connection, int)
	 */
	public int deleteVisitorDAO(Connection con, int idVisitor) throws Exception{
		int rows = 0;
		VisitorDBQuery executeQuery = new VisitorDBQuery();
		
		try{			
			//JSONQuery reader = new JSONQuery(address);
		
	    	String query = "DELETE FROM Visitor WHERE VisitorId=?;";
	    	
	    	//PreparedStatement search the query 
			PreparedStatement pStmt = con.prepareStatement(query);
		
			//Execute query in DB
	    	executeQuery.executeDeleteVisitor(con, idVisitor, pStmt);
		
			query = "DELETE FROM People WHERE idPeople=?;";
			
			//PreparedStatement search the query 
			pStmt = con.prepareStatement(query);
			
			//Execute query in DB
			rows = executeQuery.executeDeletePeople(con, idVisitor, pStmt);
			
		}catch (Exception e){
			System.out.println(e.getMessage());
		}
		return rows;
	}
	
	/**
	 *  
	 *
	 * @param con the con
	 * @param visitor the visitor
	 * @return the int
	 * @see es.ats.codington.festival.interfaces.daos.IVisitorDAO#updateVisitorDAO(java.sql.Connection, es.ats.codington.festival.domain.Visitor)
	 */
	public int updateVisitorDAO(Connection con, Visitor visitor){
		int rows = 0;
		VisitorDBQuery executeQuery = new VisitorDBQuery();
		
		try{
			//JSONQuery reader = new JSONQuery(address);
			
		    String query = "UPDATE People SET Name=?,LastName=?,Age=?,Address=?,PhoneNumber=? WHERE idPeople=?;";
			
			//Creating a PreparedStatement with a query 
			PreparedStatement pStmt = con.prepareStatement(query);
			
			executeQuery.executeUpdatePeople(con, visitor, pStmt);
			
			query = "UPDATE Visitor SET Username=?,Password=?,Admin=? WHERE VisitorId=?;";
			
			//PreparedStatement search the query 
			pStmt = con.prepareStatement(query);
			
			rows = executeQuery.executeUpdateVisitor(con, visitor, pStmt);
			
		}catch (Exception e){
			System.out.println(e.getMessage());
		}
		return rows;
	}
		
	/**
	 * Search visitor dao by id.
	 *
	 * @param con the con
	 * @param idVisitor the id visitor
	 * @return the visitor
	 * @throws Exception the exception
	 * @see es.ats.codington.festival.interfaces.daos.IVisitorDAO#searchVisitorDAOById(java.sql.Connection, int)
	 */
	public Visitor searchVisitorDAOById(Connection con, int idVisitor) throws Exception{
		Visitor visitor = new Visitor();
		VisitorDBQuery executeQuery = new VisitorDBQuery();
		
		try{			
			//JSONQuery reader = new JSONQuery(address);
		
	    	String query = "SELECT * FROM Visitor WHERE VisitorId=?;";
	    	
	    	//PreparedStatement search the query 
			PreparedStatement pStmt = con.prepareStatement(query);
		
			//Execute query in DB
	    	visitor = executeQuery.executeSearchVisitorById(con, idVisitor, pStmt);
		
			query = "SELECT * FROM People WHERE idPeople=?;";
			
			//PreparedStatement search the query 
			pStmt = con.prepareStatement(query);
			
			//Execute query in DB
			visitor = executeQuery.executeSearchPeopleById(con, idVisitor, pStmt, visitor);
			
		}catch (Exception e){
			System.out.println(e.getMessage());
		}
		return visitor;
	}
	
	/**
	 * Search visitor dao by username.
	 *
	 * @param con the con
	 * @param username the username
	 * @return the visitor
	 * @throws Exception the exception
	 * @see es.ats.codington.festival.interfaces.daos.IVisitorDAO#searchVisitorDAOByUsername(java.sql.Connection, java.lang.String)
	 */
	public Visitor searchVisitorDAOByUsername(Connection con, String username) throws Exception{
		Visitor visitor = new Visitor();
		VisitorDBQuery executeQuery = new VisitorDBQuery();
		int idVisitor = 0;
		
		try{			
		//	JSONQuery reader = new JSONQuery(address);
		
	    	String query = "SELECT * FROM visitor WHERE Username=?;";
	    	
	    	//PreparedStatement search the query 
			PreparedStatement pStmt = con.prepareStatement(query);
		
			//Execute query in DB
	    	visitor = executeQuery.executeSearchVisitorByUsername(con, username, pStmt);
	    	if (visitor.getVisitorId()!=0){
			query = "SELECT * FROM People WHERE idPeople=?;";
			idVisitor = visitor.getVisitorId();
			
			//PreparedStatement search the query 
			pStmt = con.prepareStatement(query);
			
			//Execute query in DB
			visitor = executeQuery.executeSearchPeopleById(con, idVisitor, pStmt, visitor);
	    	}
		}catch (Exception e){
			System.out.println(e.getMessage());
		}
		return visitor;
	}
	
	/**
	 * Search all visitor dao.
	 *
	 * @param con the con
	 * @return the array list
	 * @see es.ats.codington.festival.interfaces.daos.IVisitorDAO#searchAllVisitorDAO(java.sql.Connection)
	 */
	public ArrayList<Visitor> searchAllVisitorDAO(Connection con) {
	    ArrayList<Visitor> visitors = new ArrayList<Visitor>();
	    VisitorDBQuery executeQuery = new VisitorDBQuery();
		try{			
	    	String query = "SELECT * FROM Visitor;";
	    	
	    	//PreparedStatement search the query 
			PreparedStatement pStmt = con.prepareStatement(query);			
			
			//Execute query in DB
	    	visitors = executeQuery.executeSearchAllVisitors(con, pStmt);
		
			query = "SELECT * FROM People;";
			
			//PreparedStatement search the query 
			pStmt = con.prepareStatement(query);
			
			//Execute query in DB
			visitors = executeQuery.executeSearchAllPeople(con, pStmt, visitors);
			
		}catch (Exception e){
			System.out.println(e.getMessage());
		}
		return visitors;
	}
}
